package com.wiscamp.ninechapters.problems.infrastructure.mappers.sheets;

import com.wiscamp.ninechapters.problems.infrastructure.po.sheets.SheetPO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.Collection;

/**
 * 答题卡仓储对象
 *
 * @author yifeng
 */
@Repository
public interface SheetMapper extends JpaRepository<SheetPO, Long>, JpaSpecificationExecutor<SheetPO> {

    @Query(value = "select e from SheetPO e where e.taskId = :taskId")
    SheetPO findByTask(long taskId);

    /**
     * 通过用户编号获取答题卡列表
     *
     * @param userId
     * @return
     */
    @Query(value = "select e from SheetPO e where e.userId = :userId")
    Collection<SheetPO> findListByUser(long userId);

    /**
     * 分页查询
     *
     * @param spec
     * @param pageable
     * @return
     */
    Page<SheetPO> findAll(Specification<SheetPO> spec, Pageable pageable);

    //SheetPO findFirstByUserIdAndSetIdOrderBySheetIdDesc(long userId, long setId);

    //@Query(value="select e from Sheet e where e.beginTime between :startDate and :endDate and sheetStatus = :status and e.userId in (select ru.userId from RoleUser ru where ru.RoleId = :studentRoleId)")
    //@Query(nativeQuery = true, value = "select sheet_id, sheet_type, user_id, begin_time, end_time, set_id, score, sheet_status, task_id from problems.sheet e where e.begin_time between :startDate and :endDate and sheet_status = :status and e.user_id in (select ru.user_id from panels.role_user ru where ru.role_id = :studentRoleId)")
    //Collection<SheetPO> findStudentSheetsByDate(String startDate, String endDate, long studentRoleId, int status);

    //@Query(nativeQuery = true, value = "select sheet_id, sheet_type, user_id, begin_time, end_time, set_id, score, sheet_status, task_id from problems.sheet e where e.begin_time between :startDate and :endDate and e.sheet_status = :status and e.user_id in (:userIds)")
    //Collection<SheetPO> findListByStudents(String startDate, String endDate, Collection<Long> userIds, int status);

    @Query(value = "select count(e.sheetId) from SheetPO e where e.userId = :userId")
    int findSheetQuantityByUser(long userId);

    @Query(value = "select count(e.sheetId) from SheetPO e where e.beginTime between :startDate and :endDate")
    int findSheetQuantityByDate(LocalDateTime startDate, LocalDateTime endDate);
}